package uk.co.penguinpower.yasl;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class DBManager {
	Context context; // the Activity or Application that is creating an object from this class.
	
	private SQLiteDatabase db; // a reference to the database.
	private final String DB_NAME = "yasl_db"; // the name of our database
	private final int DB_VERSION = 9; // the version of the database
 
	// Stores Table
	private final String STORES_TABLE_NAME = "stores";
	private final String STORES_ROW_ID = "id"; //int
	private final String STORES_NAME = "name"; //string
	private final String STORES_LOCATION = "location"; //string
	private final String STORES_SECTIONS = "sections"; //string[]
	
	// Items Table
	private final String ITEMS_TABLE_NAME = "items";
	private final String ITEMS_ROW_ID = "id"; //int
	private final String ITEMS_NAME = "name"; //string
	private final String ITEMS_STORE_ID = "store_id"; //int
	private final String ITEMS_PRICE = "price"; //string
	private final String ITEMS_SECTION = "section"; //string
	private final String ITEMS_SIZE = "size"; //string
 
	// Item Group Table
	private final String ITEM_GROUP_TABLE_NAME = "item_group";
	private final String ITEM_GROUP_ROW_ID = "id"; //int
	private final String ITEM_GROUP_GROUP = "name"; //string
	private final String ITEM_GROUP_ITEM_IDS = "item_ids"; //int[]
	
	// Lists Table
	private final String LISTS_TABLE_NAME = "lists";
	private final String LISTS_ROW_ID = "id"; //int
	private final String LISTS_NAME = "name"; //string
	private final String LISTS_STORE_ID = "store_id"; //int
	
	// List Table
	private final String LIST_TABLE_NAME = "list_"; //+list_name
	private final String LIST_ROW_ID = "id"; //int
	private final String LIST_ITEM_ID = "item_id"; //int
	private final String LIST_PICKED = "picked"; //int
	
	public DBManager(Context context) {
		this.context = context;
 
		// create or open the database
		CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
		this.db = helper.getWritableDatabase();
	}	
	
	/**
	 * Stores Queries
	 */
	//create the Default store if the table is empty
	public void firstStore() {
		Cursor cursor;
		 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				STORES_TABLE_NAME,
				new String[] { STORES_NAME },
				null, null, null, null, null);	
			
			//move back to the start of the cursor
			if (cursor.getCount() == 0) {
				addStore("Default","0,0","All");
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
	}
	
	//add a store
	public void addStore(String name, String location, String sections) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
	 
		values.put(STORES_NAME, name);
		values.put(STORES_LOCATION, location);
		values.put(STORES_SECTIONS, sections);
	 
		// ask the database object to insert the new data 
		try {
			db.insert(STORES_TABLE_NAME, null, values);
		} catch(Exception e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
	
	//update a store
	public void updateStore(int rowID, String name, String location, String sections) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put(STORES_NAME, name);
		values.put(STORES_LOCATION, location);
		values.put(STORES_SECTIONS, sections);
	 
		// ask the database object to update the database row of given rowID
		try {
			db.update(STORES_TABLE_NAME, values, STORES_ROW_ID + "=" + rowID, null);
		} catch (Exception e) {
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	}

	//get all store names
	public ArrayList<String> getAllStores() {
		ArrayList<String> dataList = new ArrayList<String>();
		Cursor cursor;
	 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				STORES_TABLE_NAME,
				new String[] { STORES_NAME },
				null, null, null, null, null);	
			
			//move back to the start of the cursor
			if (cursor.moveToFirst()) {
				do {
					dataList.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
		
		return dataList;
	}
	
	//get a store
	public ArrayList<Object> getStoreAsArray(int rowID) {
		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor;
	 
		try {
			cursor = db.query(
				STORES_TABLE_NAME,
				new String[] { STORES_ROW_ID, STORES_NAME, STORES_LOCATION, STORES_SECTIONS },
				STORES_ROW_ID + "=" + rowID,
				null, null, null, null, null	
			);
			
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					rowArray.add(cursor.getInt(0));
					rowArray.add(cursor.getString(1));
					rowArray.add(cursor.getString(2));
					rowArray.add(cursor.getString(3));
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}	
		
		return rowArray;
	}
	
	//get a stores id
	public int getStoreID(String name) {
		int id = 0;
		Cursor cursor;
		 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				STORES_TABLE_NAME,
				new String[] { STORES_ROW_ID },
				STORES_NAME + "='" + name +"'",
				null, null, null, null, null
			);			
			//move back to the start of the cursor
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					id = cursor.getInt(0);
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
		
		return id;
	}
	
	/**
	 * Items Queries
	 */
	//add an item
	public void addItem(String name, int store_id, String price, String section, String size) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
	 
		values.put(ITEMS_NAME, name);
		values.put(ITEMS_STORE_ID, store_id);
		values.put(ITEMS_PRICE, price);
		values.put(ITEMS_SECTION, section);
		values.put(ITEMS_SIZE, size);
	 
		// ask the database object to insert the new data 
		db.insert(ITEMS_TABLE_NAME, null, values);
	}
	
	//get an item
	public ArrayList<Object> getItemAsArray(String item) {
		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor;
	 
		try {
			cursor = db.query(
				ITEMS_TABLE_NAME,
				new String[] { ITEMS_ROW_ID, ITEMS_NAME, ITEMS_STORE_ID, ITEMS_PRICE, ITEMS_SECTION, ITEMS_SIZE },
				ITEMS_NAME + "='" + item +"'",
				null, null, null, null, null	
			);
			
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					rowArray.add(cursor.getInt(0));
					rowArray.add(cursor.getString(1));
					rowArray.add(cursor.getInt(2));
					rowArray.add(cursor.getString(3));
					rowArray.add(cursor.getString(4));
					rowArray.add(cursor.getString(5));
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}	
		
		return rowArray;
	}
	
	/**
	 * Item Group Queries
	 */
	//add item_group
	public void addItemGroup(String group, int[] item_ids) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
	 
		values.put(ITEM_GROUP_GROUP, group);
		values.put(ITEM_GROUP_ITEM_IDS, commonFuncs.intArrayToString(item_ids,","));
			 
		// ask the database object to insert the new data 
		try {
			db.insert(ITEM_GROUP_TABLE_NAME, null, values);
		} catch(Exception e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
	
	//get item_group
	public int[] getItemGroup(String group) {
		ArrayList<String> rowArray = new ArrayList<String>();
		Cursor cursor;
		int item_ids[] = {0};
		int i, i2;
		String s, splitt[];
		
	 
		try {
			cursor = db.query(
				ITEM_GROUP_TABLE_NAME,
				new String[] { ITEM_GROUP_ITEM_IDS },
				ITEM_GROUP_GROUP + "='" + group +"'",
				null, null, null, null, null	
			);
			
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					rowArray.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			
			//convert string in rowArray to int[]
			for (i=0; i<rowArray.size(); i++) {
				s = rowArray.get(i);
				splitt = s.split(",");
				
				for (i2=0; i2<splitt.length; i2++) { 
					item_ids[i2] = Integer.parseInt(splitt[i2]);
				}
			}			
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}	
		
		
		return item_ids;
	}
	
	//get all item_group names
	public ArrayList<String> getAllItemGroups() {
		ArrayList<String> dataList = new ArrayList<String>();
		Cursor cursor;
	 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				ITEM_GROUP_TABLE_NAME,
				new String[] { ITEM_GROUP_GROUP },
				null, null, null, null, null, null
			);			
			//move back to the start of the cursor
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					dataList.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
		
		return dataList;
	}
	
	/**
	 * Lists Queries
	 */
	//create the "No list Selected" list if the table is empty
	public void firstList() {
		Cursor cursor;
		 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				LISTS_TABLE_NAME,
				new String[] { LISTS_NAME },
				null, null, null, null, null);	
			
			//move back to the start of the cursor
			if (cursor.getCount() == 0) {
				addList("No list selected", getStoreID("Default"));
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
	}
	
	//add list
	public void addList(String name, int store_id) {
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
	 
		values.put(LISTS_NAME, name);
		values.put(LISTS_STORE_ID, store_id);
	 
		// ask the database object to insert the new data 
		try {
			db.insert(LISTS_TABLE_NAME, null, values);
		} catch(Exception e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
	
	//get all list names
	public ArrayList<String> getAllLists() {
		ArrayList<String> dataList = new ArrayList<String>();
		Cursor cursor;
	 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				LISTS_TABLE_NAME,
				new String[] { LISTS_NAME },
				null, null, null, null, null, null
			);			
			//move back to the start of the cursor
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					dataList.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
		
		return dataList;
	}
	
	//get a list
	public int getListStoreID(String name) {
		int storeID = 0;
		Cursor cursor;
	 
		try {
			//fill cursor with the results of the query
			cursor = db.query (
				LISTS_TABLE_NAME,
				new String[] { LISTS_STORE_ID },
				LISTS_NAME + "='" + name + "'",
				null, null, null, null
			);			
			//move back to the start of the cursor
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()) {
				do {
					storeID = cursor.getInt(0);
				} while (cursor.moveToNext());
			}
			
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}		
		
		return storeID;
	}
	
	/**
	 * List Queries
	 */
	//create a new list table
	public void createListTable(String name) {
		String listTableQueryString = 	
			"create table " +
			LIST_TABLE_NAME + name +
			" (" +
			LIST_ROW_ID + " integer primary key autoincrement not null," +
			LIST_ITEM_ID + " integer," +
			LIST_PICKED + " integer" +
			");";
		db.execSQL(listTableQueryString);
	}
	
	//delete a list table
	public void deleteListTable(String name) {
		String deleteTableQueryString = 
			"drop table if exists " +
			LIST_TABLE_NAME + name;
		try {
			db.execSQL(deleteTableQueryString);
		} catch(SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
	
	//get list
	public ArrayList<Object> getList(String name) {
		ArrayList<Object> dataArrays = new ArrayList<Object>();
		Cursor cursor;
	 
		try {
		    cursor = db.query(
					LIST_TABLE_NAME + name,
					new String[] { LIST_ITEM_ID, LIST_PICKED },
					null,null, null, null, null, null
			);
		    
		    // move the cursor's pointer to position zero.
			cursor.moveToFirst();
	 
			// if there is data after the current cursor position, add it
			// to the ArrayList.
			if (!cursor.isAfterLast()) {
				do {
					ArrayList<Object> dataList = new ArrayList<Object>();
	 
					dataList.add(cursor.getInt(0));
					dataList.add(cursor.getInt(1));
	 
					dataArrays.add(dataList);
				}
				// move the cursor's pointer up one position.
				while (cursor.moveToNext());
			}
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	 
		return dataArrays;
	}
	
	/**
	 * Common Queries 
	 */
	//delete a row
	public void deleteRow(int rowID, String table) {
		try {
			db.delete(table, "id =" + rowID, null);
		} catch (Exception e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
	
	/**
	 * This class is designed to check if there is a database that currently
	 * exists for the given program.  If the database does not exist, it creates
	 * one.  After the class ensures that the database exists, this class
	 * will open the database for use.  Most of this functionality will be
	 * handled by the SQLiteOpenHelper parent class.  The purpose of extending
	 * this class is to tell the class how to create (or update) the database.
	 */
	private class CustomSQLiteOpenHelper extends SQLiteOpenHelper {		
		@Override
		public void onCreate(SQLiteDatabase db) {
			//create the Stores table
			String storesTableQueryString =
				"create table " +
				STORES_TABLE_NAME +
				" (" +
				STORES_ROW_ID + " integer primary key autoincrement not null," +
				STORES_NAME + " text," +
				STORES_LOCATION + " text," +
				STORES_SECTIONS + " text" +
				");";
			db.execSQL(storesTableQueryString);
			
			//create the Items table
			String itemsTableQueryString = 	
				"create table " +
				ITEMS_TABLE_NAME +
				" (" +
				ITEMS_ROW_ID + " integer primary key autoincrement not null," +
				ITEMS_NAME + " text," +
				ITEMS_STORE_ID + " integer," +
				ITEMS_PRICE + " text," +
				ITEMS_SECTION + " text," +
				ITEMS_SIZE + " text" +
				");";
			db.execSQL(itemsTableQueryString);
			
			//create the Item Groups table
			String itemGroupTableQueryString = 	
				"create table " +
				ITEM_GROUP_TABLE_NAME +
				" (" +
				ITEM_GROUP_ROW_ID + " integer primary key autoincrement not null," +
				ITEM_GROUP_GROUP + " text," +
				ITEM_GROUP_ITEM_IDS + " text" +
				");";
			db.execSQL(itemGroupTableQueryString);
			
			//create the Lists table
			String listsTableQueryString = 	
				"create table " +
				LISTS_TABLE_NAME +
				" (" +
				LISTS_ROW_ID + " integer primary key autoincrement not null," +
				LISTS_NAME + " text," +
				LISTS_STORE_ID + " integer" +
				");";
			db.execSQL(listsTableQueryString);
		}
		
		
		public CustomSQLiteOpenHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			if (newVersion == 9) {
				int i;
				//clean up the shit
				String[] deleteTableQueryString = { 
					"drop table if exists " + STORES_TABLE_NAME,
					"drop table if exists " + ITEMS_TABLE_NAME,
					"drop table if exists " + ITEM_GROUP_TABLE_NAME,
					"drop table if exists " + LISTS_TABLE_NAME
				};
				try {
					for (i=0; i<deleteTableQueryString.length; i++) {
						db.execSQL(deleteTableQueryString[i]);
					}
				} catch(SQLException e) {
					Log.e("DB ERROR", e.toString());
					e.printStackTrace();
				}
				//create the Stores table
				String storesTableQueryString =
					"create table " +
					STORES_TABLE_NAME +
					" (" +
					STORES_ROW_ID + " integer primary key autoincrement not null," +
					STORES_NAME + " text," +
					STORES_LOCATION + " text," +
					STORES_SECTIONS + " text" +
					");";
				db.execSQL(storesTableQueryString);
				
				//create the Items table
				String itemsTableQueryString = 	
					"create table " +
					ITEMS_TABLE_NAME +
					" (" +
					ITEMS_ROW_ID + " integer primary key autoincrement not null," +
					ITEMS_NAME + " text," +
					ITEMS_STORE_ID + " integer," +
					ITEMS_PRICE + " text," +
					ITEMS_SECTION + " text," +
					ITEMS_SIZE + " text" +
					");";
				db.execSQL(itemsTableQueryString);
				
				//create the Item Groups table
				String itemGroupTableQueryString = 	
					"create table " +
					ITEM_GROUP_TABLE_NAME +
					" (" +
					ITEM_GROUP_ROW_ID + " integer primary key autoincrement not null," +
					ITEM_GROUP_GROUP + " text," +
					ITEM_GROUP_ITEM_IDS + " text" +
					");";
				db.execSQL(itemGroupTableQueryString);
				
				//create the Lists table
				String listsTableQueryString = 	
					"create table " +
					LISTS_TABLE_NAME +
					" (" +
					LISTS_ROW_ID + " integer primary key autoincrement not null," +
					LISTS_NAME + " text," +
					LISTS_STORE_ID + " integer" +
					");";
				db.execSQL(listsTableQueryString);
			}			
		}
	}
}